package com.lyf.util;

import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.*;

import com.lyf.annotation.ExcelFiled;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;

/**
 * excel解析工具
 * @author linyufeng
 * https://blog.csdn.net/linfujian1999/article/details/86594177
 */
public class ExcelUtil {

    /**
     * 根据clazz定义的column解析excel文件
     *
     * @param excel
     * @param clazz
     */

    public static <T> List<T> parse(MultipartFile excel, Class<T> clazz) {
        try {
            File tmpFile = File.createTempFile(excel.getOriginalFilename().substring(0,
                    excel.getOriginalFilename().lastIndexOf(".")),
                    excel.getOriginalFilename().substring(excel.getOriginalFilename().lastIndexOf(".") + 1));
            excel.transferTo(tmpFile);
            Workbook workbook = getWorkBook(tmpFile);
            Sheet sheet = workbook.getSheetAt(0);
            return parseSheet(sheet, clazz);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    private static Workbook getWorkBook(File file) {
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
            if (file.getName().endsWith("xls")) {
                return new HSSFWorkbook(fis);
            }
            if (file.getName().endsWith("xlsx")) {
                return new XSSFWorkbook(fis);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(fis);
        }
        return null;
    }

    private static <T> List<T> parseSheet(Sheet sheet, Class<T> clazz) {
        List<T> result = new ArrayList<>();
        Map<String, Map> field2ColNum = parseAnnotation(clazz);
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            try {
                T object = clazz.newInstance();
                for (String filedName : field2ColNum.keySet()) {
                    Map conf = field2ColNum.get(filedName);
                    int cellNum = (int) conf.get("index");
                    Class type = (Class) conf.get("type");
                    Cell cell = row.getCell(cellNum);
                    String pattern = conf.get("pattern").toString();
                    Object value = getCellValue(cell, pattern);
                    if(cell!=null){
                        PropertyDescriptor propDesc = new PropertyDescriptor(filedName, clazz);
                        Method method = propDesc.getWriteMethod();
                        method.invoke(object, convertType(type, value.toString(), pattern));
                    }
                }
                result.add(object);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 将list中的数据写入excel
     *
     * @param list
     * @return
     */
    public static <T> InputStream exportExcel(List<T> list) {
        InputStream excel = null;
        if (list.isEmpty()) {
            return excel;
        }

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 设置默认宽度
        // sheet.setDefaultColumnWidth(12);
        Class<?> clazz = list.get(0).getClass();
        Map<String, Map> field2ColNum = parseAnnotation(clazz);

        Set<String> fileds = field2ColNum.keySet();
        for (int i = -1; i < list.size(); i++) {
            Row row = sheet.createRow(i+1);

            for (String filedName : fileds) {
                try {
                    Map conf = field2ColNum.get(filedName);
                    int index = (int) conf.get("index");
                    String name = (String) conf.get("name");
                    String pattern = (String) conf.get("pattern");
                    if(i == -1) {
                        // Excel 标题头
                        row.createCell(index).setCellValue(name);
                        // 根据标题自适应
                        // sheet.setColumnWidth(index, name.getBytes().length*256);
                    }else{
                        PropertyDescriptor propDesc = new PropertyDescriptor(filedName, clazz);
                        Method method = propDesc.getReadMethod();
                        Object value = method.invoke(list.get(i));
                        row.createCell(index).setCellValue(convertType(value, pattern));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

        // 根据内容自适应
        // for (int i = 0; i < fields.length; i++) {
        //     sheet.autoSizeColumn(i);
        // }

        try {
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            excel = new ByteArrayInputStream(outputStream.toByteArray());
            workbook.close();
            return excel;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return excel;
    }

    public static void exportExcel(HttpServletResponse response, List list, String fielname) {

        InputStream in = ExcelUtil.exportExcel(list);
        response.reset();
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("UTF-8");
        try {
            fielname = URLEncoder.encode(fielname + ".xlsx", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-Disposition", "attachment; filename=" + fielname);
        byte[] buffer = new byte[1024];
        int length;
        try {
            while ((length = in.read(buffer)) > 0) {
                response.getOutputStream().write(buffer, 0, length);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(in);
        }
    }

    /**
     * 类型转换
     *
     * @param classzz
     * @param value
     * @param pattern
     * @return
     *
     * https://blog.csdn.net/qq_39623859/article/details/80881906
     */
    private static Object convertType(Class classzz, String value, String pattern) {
        if (Integer.class == classzz || int.class == classzz) {
            return Integer.valueOf(value);
        }
        if (Short.class == classzz || short.class == classzz) {
            return Short.valueOf(value);
        }
        if (Byte.class == classzz || byte.class == classzz) {
            return Byte.valueOf(value);
        }
        if (Character.class == classzz || char.class == classzz) {
            return value.charAt(0);
        }
        if (Long.class == classzz || long.class == classzz) {
            return Long.valueOf(value);
        }
        if (Float.class == classzz || float.class == classzz) {
            return Float.valueOf(value);
        }
        if (Double.class == classzz || double.class == classzz) {
            return Double.valueOf(value);
        }
        if (Boolean.class == classzz || boolean.class == classzz) {
            return Boolean.valueOf(value.toLowerCase());
        }
        if (BigDecimal.class == classzz) {
            return new BigDecimal(value);
        }
        if (Date.class == classzz) {
            SimpleDateFormat formatter = new SimpleDateFormat(pattern);
            ParsePosition pos = new ParsePosition(0);
            Date date = formatter.parse(value, pos);
            return date;
        }
        return value;
    }

    /**
     * 类型转换
     *
     * @param value
     * @return
     */
    private static String convertType(Object value, String pattern) {
        if (value instanceof Date) {
            SimpleDateFormat sdf = new SimpleDateFormat(pattern);//日期格式化
            return sdf.format(value);
        }
        return value.toString();
    }

    /**
     * 获取每个单元格的内容
     * @param cell
     * @param pattern
     * @return
     *
     * https://blog.csdn.net/fwk19840301/article/details/81878130
     */
    private static Object getCellValue(Cell cell, String pattern) {
        Object value = null;

        DecimalFormat df = new DecimalFormat("0");//格式化number String字符串
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);//日期格式化

        switch (cell.getCellTypeEnum()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = sdf.format(cell.getDateCellValue());
                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else {
                    value = cell.getRichStringCellValue().getString();
                }
                break;
            case BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;

    }

    /**
     * 解析注解
     * @param clazz
     * @return
     */
    private static Map<String, Map> parseAnnotation(Class<?> clazz){

        Map<String, Map> field2ColNum = new HashMap<>();
        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields) {
            if (!field.isAccessible()) {
                field.setAccessible(true);
            }
            ExcelFiled anno = field.getDeclaredAnnotation(ExcelFiled.class);
            if (null != anno) {
                Map conf = new HashMap();
                conf.put("index", anno.index());
                conf.put("name", anno.name());
                conf.put("pattern", anno.pattern());
                conf.put("type", field.getType());
                field2ColNum.put(field.getName(), conf);
            }
        }
        return field2ColNum;
    }
}

